const mysql = require('mysql');

//  封装一个query方法
const dbconfig = {
    host: '127.0.0.1',
    user: 'root',
    password: '',
    database: 'cxblog',
    port: '3306',
    // 可以同时写多条sql语句
    multipleStatements: true
}

const pool = mysql.createPool(dbconfig)

function query(sql, params) {
    return new Promise(function(resolve, reject) {
        pool.getConnection(function(err, conn) {
            if (err) reject(err);
            conn.query(sql, params, function(err, rows) {
                if (err) reject(err);
                resolve(rows);
                conn.release();
            })
        })
    })
}


//=-------------------------------菜单管理---------------------------
exports.getAllMenu=function(){
    let sql='select * from menu'
    return query(sql)
}

//提交新增菜单
exports.addMenu=function(menu){
    let sql='insert into menu set ?'
    return query(sql,menu)
   
    
}
//编辑菜单
exports.getOneMenu=function(id){
    let sql='select * from menu where menuId =?'
    return query(sql,id)
}
//提交编辑菜单
exports.editMenu=function(menu){
    let sql='update menu set menuName=?,linkUrl=?,status=? where menuId=?'
    let params=[menu.menuName,menu.linkUrl,menu.status,menu.menuId];
    return query(sql,params)
}
//删除菜单
exports.deleteMenu=function(id){
    let sql='delete from menu where menuId=?'
    return query(sql,id)
}

//---------------------文章分类------------------
exports.getAllClass=function(){
    let sql='select * from classify'
    return query(sql)
}
//添加文章
exports.addClass=function(class1){
    let sql='insert into classify set ?'
    return query(sql,class1)
}

//编辑文章
exports.getOneClass=function(id){
    let sql='select * from classify where classId=?'
    return query(sql,id)
}

//提交编辑数据
exports.editClass=function(class2){
    let sql='update classify set className=?,status=? where classId=?'
    
    let params=[class2.className,class2.status,class2.classId]
    return query(sql,params)
}

//删除文章
exports.deleteClass=function(id){
    let sql='delete from classify where classId=?'
    return query(sql,id)
}

//---------用户管理-----------
exports.getAllUser=function(currentPage){
    currentPage=currentPage||1;
    let start=(currentPage-1)*5;
    let sql = 'select * from user limit ?,5 ;select count(*) as total from user';
    return query(sql,start)
}
//娴后台首页
exports.getNewArtical = function() {
    let sql = 'SELECT articalId,articalName,status,(select userName  from user where userId= artical.userId) as userName,(select className  from classify where classId= artical.classId) as className,updateTime  from artical order by updateTime desc limit 0,5;';
    return query(sql);
}

exports.login = function(user) {
    let sql = 'select * from user where username=? and password=?'
    let params = [user.username, user.password];
    return query(sql, params);

}

//---------用户管理-----------
exports.getAllUser = function(currentPage) {
        currentPage = currentPage || 1;
        let start = (currentPage - 1) * 5;
        let sql = 'select * from user limit ?,5 ;select count(*) as total from user';
        return query(sql, start)
    }
    // -------用户模块--------------------

//新增
exports.getOneUser = function(user) {
    let sql = 'insert into user set ?'
    return query(sql, user)
}

//删除
exports.deleteUser = function(id) {
    let sql = 'delete from user where userId = ?';
    return query(sql, id);
}

//修改
exports.getOneuser = function(id) {
    let sql = 'select * from user where userId = ?';
    return query(sql, id);
}

exports.editUser = function(user) {
    let sql = 'update user set username=?,password=?,email=?,status=? where userId=?';
    let params = [user.username, user.password, user.email, user.status, user.userId]
    return query(sql, params);
}

exports.search = function(txt) {
    let sql = 'select * from user where username=?';
    return query(sql, txt)
}


// ----------------------轮播图模块---------------------------
exports.getAllBanner = function() {
    let sql = 'select *from banner';
    return query(sql)
}
exports.addBanner = function(banner) {
    let sql = 'insert into banner set ?';
    // console.log(banner);
    return query(sql, banner)
}
exports.editBannerPage = function(id) {
    let sql = 'select *from banner where bannerId';
    return query(sql, id)
}
exports.editBanner = function(banner) {
    console.log(banner);
    let sql = 'update banner set imgName=?,linkUrl=?,status=? where bannerId=?';
    let params = [banner.imgName, banner.linkUrl, banner.status, banner.bannerId]
    return query(sql, params)

}
exports.deleteBanner = function(id) {
    let sql = 'delete from banner where bannerId=?';
    return query(sql, id)
}

// ------------文章管理-------------

exports.getAllAticles = (currentPage) => {
    currentPage = currentPage || 1;
    let start = (currentPage - 1) * 5;
    let sql = 'SELECT artical.articalId,artical.articalName,artical.createTime,artical.status,user.userName,classify.className FROM artical, user,classify  where  artical.userId = user.userId and classify.classId= artical.classId  limit ?,5;select count(*) as total from artical; ';
    // console.log('开始查询');

    return query(sql, start);
}
exports.getAllClasses = () => {
    let sql = 'select className, classId from classify';
    return query(sql);
}
exports.updateArticle = (article) => {
    let sql = 'insert into artical set ?';
    return query(sql, article);
}
exports.getOneEditArtical = (articalId) => {
    let sql = 'select * from artical where articalId = ?;select className, classId from classify';
    return query(sql, articalId);
}
exports.editArtical = function(artical) {
    let sql = "update artical set articalName= ?,content =? ,classId=? where articalId = ?";
    let params = [artical.articalName, artical.content, artical.classId, artical.articalId];
    return query(sql, params);
}
exports.deleteArtical = (articalId) => {
    let sql = 'delete from artical where articalId = ?';
    return query(sql, articalId);
}